package com.css.fxfzfxqh.modules.buildings.definitiveEvaluation.resistEarthquakesDE.repository.impl;

import com.css.fxfzfxqh.modules.buildings.definitiveEvaluation.resistEarthquakesDE.entity.EarthquakeAffectedField;
import com.css.fxfzfxqh.modules.buildings.definitiveEvaluation.resistEarthquakesDE.entity.ResistEarthquakesCalculation;
import com.css.fxfzfxqh.modules.buildings.definitiveEvaluation.resistEarthquakesDE.entity.ResistEarthquakesDE;
import com.css.fxfzfxqh.modules.buildings.definitiveEvaluation.resistEarthquakesDE.param.ResistEarthquakesCalculationParam;
import com.css.fxfzfxqh.modules.buildings.definitiveEvaluation.resistEarthquakesDE.param.AssessParam;
import com.css.fxfzfxqh.modules.buildings.definitiveEvaluation.resistEarthquakesDE.repository.ResistEarthquakesDERepository;
import com.css.fxfzfxqh.modules.buildings.definitiveEvaluation.resistEarthquakesDE.vo.*;
import com.css.fxfzfxqh.modules.largeScreen.entity.Calculation;
import com.css.fxfzfxqh.util.PlatformObjectUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: zhangSongRui
 * @CreateTime: 2023/05/24
 * @Description: 抗震能力_确定评估
 */
@Repository
public class ResistEarthquakesDERepositoryImpl implements ResistEarthquakesDERepository {

    @Resource
    JdbcTemplate jdbcTemplate;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    //抗震能力_正确评估_评估记录主表
    private static final String TABLE_NAME = "fxqh_resist_earthquakes_assess_ed";
    //抗震能力_正确评估_评估记录_计算(烈度,概率)(地理id,房屋单体id) 合并为一个字段
    private static final String TABLE_CALCULATION = "fxqh_resist_earthquakes_calculation_ed";

    //保存评估记录
    @Override
    public void addAssess(ResistEarthquakesDE param) {
        String sql = "insert into "+TABLE_NAME+"(id,assess_code,assess_name,assess_desc,house_data,house_data_flag," +
                "coordinate_range,calculation,create_user,create_time,del_flag,seismic_input_mode,radio2,area_range) " +
                "values(:id,:assessCode,:assessName,:assessDesc,:houseData,:houseDataFlag," +
                ":coordinateRange,:calculation,:createUser,:createTime,:delFlag,:seismicInputMode,:radio2,:areaRange)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
        namedParameterJdbcTemplate.update(sql, source);
    }


    //根据条件查询计算值
    @Override
    public Map<String, Object>  queryCalculation(String seismicInputMode,int curPage, int pageSize,String id, String condition, String damageDegree) {
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        StringBuilder sql = new StringBuilder("select id,assess_id,assess_code,province_name,city_name," +
                "county_name,geography_id,condition,damage_degree,calculated_value,correction_value," +
                "coordinate_range,create_user,create_time,intensity from "+TABLE_CALCULATION+" where 1=1  ");
        if (!PlatformObjectUtils.isEmpty(id)) {
            sql.append(" and assess_id = '"+id+"'");
        }
        //条件
        if (seismicInputMode.equals("2")) {
            sql.append(" and version = '" + condition + "' ");
        }else {
            sql.append(" and condition ='"+condition+"' ");
        }
        //破坏状态不为null
        if (!PlatformObjectUtils.isEmpty(damageDegree)) {
            sql.append(" and damage_degree = '"+damageDegree+"'");
        }
        StringBuilder orderSql = new StringBuilder(" order by county_name,geography_id desc");
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(ResistEarthquakesCalculationVO.class)).size();
        List<ResistEarthquakesCalculationVO> list = jdbcTemplate.query(sql.append(orderSql).append(limitSql).toString(), new BeanPropertyRowMapper<>(ResistEarthquakesCalculationVO.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public Map<String, Object> getPage(int curPage, int pageSize, AssessParam param) {
        StringBuilder sql = new StringBuilder("select * from " + TABLE_NAME);
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0'");
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        //评估记录编号
        if (!PlatformObjectUtils.isEmpty(param.getAssessCode())) {
            whereSql.append(" and assess_code = '" + param.getAssessCode() + "'");
        }
        //评估记录名称
        if (!PlatformObjectUtils.isEmpty(param.getAssessName())) {
            whereSql.append(" and assess_name = '" + param.getAssessName() + "'");
        }
        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(ResistEarthquakesDE.class)).size();
        List<ResistEarthquakesDE> list = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(ResistEarthquakesDE.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    //修改 经验评估与修正值
    @Override
    public void updateCorrectionValue(ResistEarthquakesCalculation param) {
        String sql = "update " + TABLE_CALCULATION + " set correction_value=:correctionValue," +
                "update_user = :updateUser ,update_time = :updateTime where id = :id";
        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public List<ResistEarthquakesPCCVO> getListPCC(ResistEarthquakesCalculationParam param) {
        StringBuilder sql = new StringBuilder("select province_name,city_name,county_name,extends1,condition,damage_degree,calculated_value,correction_value from "+TABLE_CALCULATION);
        StringBuilder whereSql = new StringBuilder(" where 1 = 1");
        //评估记录id
        if (!PlatformObjectUtils.isEmpty(param.getId())) {
            whereSql.append(" and assess_id = '" + param.getId() + "'");
        }
        //条件(地震烈度 或 四个概率水准)
        if (!PlatformObjectUtils.isEmpty(param.getCondition())) {
            whereSql.append(" and condition = '" + param.getCondition() + "'");
        }
        //破坏程度
        if (!PlatformObjectUtils.isEmpty(param.getDamageDegree())) {
            whereSql.append(" and damage_degree = '" + param.getDamageDegree() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getProvince())){
            whereSql.append(" and province_name = '" + param.getProvince() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getCity())){
            whereSql.append(" and city_name = '" + param.getCity() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getCounty())){
            whereSql.append(" and county_name = '" + param.getCounty() + "'");
        }
        List<ResistEarthquakesPCCVO> list = jdbcTemplate.query(sql.append(whereSql).append(" order by province_name,city_name,county_name,extends1,condition,damage_degree").toString(), new BeanPropertyRowMapper<>(ResistEarthquakesPCCVO.class));
        return list;
    }

    @Override
    public List<ResistEarthquakesXYVO> getListXY(ResistEarthquakesCalculationParam param) {
        StringBuilder sql = new StringBuilder("select * from "+TABLE_CALCULATION);
        StringBuilder whereSql = new StringBuilder(" where 1 = 1");
        //评估记录id
        if (!PlatformObjectUtils.isEmpty(param.getId())) {
            whereSql.append(" and assess_id = '" + param.getId() + "'");
        }
        //条件(地震烈度 或 四个概率水准)
        if (!PlatformObjectUtils.isEmpty(param.getCondition())) {
            whereSql.append(" and condition = '" + param.getCondition() + "'");
        }
        //破坏程度
        if (!PlatformObjectUtils.isEmpty(param.getDamageDegree())) {
            whereSql.append(" and damage_degree = '" + param.getDamageDegree() + "'");
        }
        List<ResistEarthquakesXYVO> list = jdbcTemplate.query(sql.append(whereSql).toString(), new BeanPropertyRowMapper<>(ResistEarthquakesXYVO.class));
        return list;
    }

    //批量新增计算值
    @Override
    public void batchCalculation(List<ResistEarthquakesCalculation> list) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray());
        String sql = "INSERT INTO "+TABLE_CALCULATION+" (id,assess_id,assess_code,province_name,city_name," +
                "county_name,geography_id,condition,damage_degree,calculated_value,correction_value,coordinate_range,create_user,create_time,geom,area,extends1,version,intensity)" +
                "values (:id,:assessId,:assessCode,:provinceName,:cityName," +
                ":countyName,:geographyId,:condition,:damageDegree,:calculatedValue,:correctionValue,:coordinateRange,:createUser,:createTime,ST_GeometryFromText(:geom,4490),:area,:extends1,:version,:intensity)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    @Override
    public String queryLatest(StringBuilder code) {
        StringBuilder sql = new StringBuilder("select assess_code from " + TABLE_NAME +" where del_flag = '0' and assess_code like '%"+code+"%' order by create_time desc limit 1");
        try {
            return jdbcTemplate.queryForObject(sql.toString(), String.class);
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    //根据id查询评估记录
    @Override
    public ResistEarthquakesDE findById(String id) {
        String sql="select * from "+TABLE_NAME+" where id = ? ";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ResistEarthquakesDE.class), id);
    }

    //修改评估记录
    @Override
    public void upDateAssess(ResistEarthquakesDE param) {
        String sql = "update " + TABLE_NAME +
                " set assess_name = :assessName,assess_desc = :assessDesc," +
                "update_user=:updateUser,update_time=:updateTime where del_flag = '0' and id = '"+param.getId()+"'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
        namedParameterJdbcTemplate.update(sql, source);
    }

    //根据条件删除计算数据
    @Override
    public void deleteCalculationByQuery(ResistEarthquakesCalculation obj) {
        String sql = "delete from "+TABLE_CALCULATION+" where assess_id='"+obj.getAssessId()+
                "' and condition = '"+obj.getCondition()+
                "' and county_name = '"+obj.getCountyName()+
                "' and damage_degree ='"+obj.getDamageDegree()+"'";
        jdbcTemplate.update(sql);
    }

    //根据id查询地震影响场
    @Override
    public EarthquakeAffectedField queryInfluenceField(String field) {
        String sql="select fsi_id,area,intensity from xzch_fast_seismic_influence where fsi_id = ? ";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(EarthquakeAffectedField.class), field);
    }

    //根据版本查询影响场
    @Override
    public List<EarthquakeAffectedField> queryInfluenceFieldByVersion(String condition) {
        String sql="select fsi_id,area,intensity from xzch_fast_seismic_influence where fv_id = '"+condition+"'";
       return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(EarthquakeAffectedField.class));
    }

    @Override
    public List<ResistEarthquakesCalculation> findByAssessId(String id) {
        String sql="select * from "+TABLE_CALCULATION+" where assess_id= '"+id+"'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ResistEarthquakesCalculation.class));
    }

    @Override
    public void deleteSmallField(ArrayList<String> idList) {
        StringBuilder sql = new StringBuilder("delete from "+TABLE_CALCULATION+" where id in (:ids)");
        Map idMap = new HashMap();
        idMap.put("ids", idList);
        namedParameterJdbcTemplate.update(sql.toString(), idMap);
    }

    @Override
    public List<Map<String, Object>> getSumByDivisionAndAssessCodes(String province, String city, String county,
                                                                    String taskId, String condition,String seiMode) {

        String sql = "";
        //地震影响场
        if("2".equals(seiMode)){
            sql ="select sum(cast(t1.correction_value as float)) sum,count(t1.assess_id) count,t1.damage_degree damageDegree,t1.extends1 extends1" +
                    ",t1.area area from fxqh_resist_earthquakes_calculation_ed t1" +
                    " where t1.province_name='"+province+"' and t1.city_name='"+city+"' and t1.county_name='"+county+"' " +
                    "and t1.version='"+condition+"' and t1.assess_id='"+taskId+"'" +
                    " group by t1.extends1,t1.damage_degree,t1.area order by count(t1.assess_id) desc";
        }
        //
        else {
            sql ="select sum(cast(t1.correction_value as float)) sum,count(t1.assess_id) count,t1.damage_degree damageDegree,t1.extends1 extends1" +
                    ",t1.area area from fxqh_resist_earthquakes_calculation_ed t1" +
                    " where t1.province_name='"+province+"' and t1.city_name='"+city+"' and t1.county_name='"+county+"' " +
                    "and t1.condition='"+condition+"' and t1.assess_id='"+taskId+"'" +
                    " group by t1.extends1,t1.damage_degree,t1.area order by count(t1.assess_id) desc";
        }
        return jdbcTemplate.queryForList(sql);
    }

    @Override
    public Integer getCountByAssessId(String assessId){
        String sql = "SELECT count(*) FROM fxqh_resist_earthquakes_calculation_ed where assess_id='"+assessId+"'";
        return jdbcTemplate.queryForObject(sql,Integer.class);
    }

    @Override
    public List<Calculation> getValue(List<String> taskIdList, String code) {
        String sql = "";
        if("0".equals(code)){
            sql = "select avg(correction_value) val,province_name areaName ,case when intensity='ld1' then '1'  when intensity='ld2' then '2' " +
                    " when intensity='ld3' then '3' when intensity='ld4' then '4'  when intensity='ld5' then '5' else 0 end intensity " +
                    " from fxqh_resist_earthquakes_calculation_ed where assess_id in ('"+String.join("','", taskIdList)+"') and intensity in ('ld1','ld2','ld3','ld4','ld5') GROUP BY intensity,province_name order by val";
        }else{
            sql = "select avg(correction_value) val,city_name as area_name from fxqh_resist_earthquakes_calculation_ed where assess_id in ('"+String.join("','", taskIdList)+"') GROUP BY area_name order by val";
        }
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(Calculation.class));
    }
}
